/*
 * 中北大学软件学院版权所有2020-2025
 */
package nuc.lp.uolab.openclass.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import nuc.lp.uolab.openclass.vo.OpenClass;
import nuc.lp.uolab.util.DbUtil;

/**
 * @author liupeng
 * @version 1.0
 */
public class OpenClassDaoImpl implements OpenClassDao{

	@Override
	public boolean insertOpenclass(OpenClass openclass) {
		boolean flag = false;
		Connection connection = null;
		PreparedStatement pStatement = null;

		connection = DbUtil.getConnection();
		String sql = "INSERT INTO t_openclass VALUES(?,?,?,?,?,?,?,?,?,?,?)";
		try {
			pStatement = connection.prepareStatement(sql);
			pStatement.setInt(1, openclass.getCid());
			pStatement.setString(2, openclass.getDate());
			pStatement.setString(3, openclass.getSpecial());
			pStatement.setString(4, openclass.getSpeaker());
			pStatement.setString(5, openclass.getOrganizer());
			pStatement.setString(6, openclass.getSite());
			pStatement.setString(7, openclass.getGroupShow());
			pStatement.setString(8, openclass.getWay());
			pStatement.setInt(9, openclass.getCredit());
			pStatement.setInt(10, openclass.getNum());
			pStatement.setString(11, openclass.getRemark());

			int cont = pStatement.executeUpdate();
			if (cont != 0) {
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtil.closeResource(connection, pStatement, null);
		}
		return flag;
	}

	@Override
	public List<OpenClass> selectOpenclass(String date, String dName, String major, String grade) {
		Connection connection = DbUtil.getConnection();
		Statement stmt = null;// 非预编译对象
		ResultSet rSet = null;
		List<OpenClass> openclasses = new ArrayList<OpenClass>();
		// 不知道集体的查询，所以SQL是动态变化的
		String sql = "SELECT  t_openclass.*, \r\n"
				+ "		t_labmember.stu_name, t_labmember.grade FROM t_openclass \r\n"
				+ "		INNER JOIN t_openclass_member ON t_openclass_member.cid = t_openclass.cid \r\n"
				+ "		INNER JOIN t_labmember ON t_openclass_member.member_id = t_labmember.member_id\r\n"
				+ "		INNER JOIN t_class ON t_labmember.cno = t_class.cno \r\n"
				+ "		INNER JOIN t_major ON t_class.major_id = t_major.major_id \r\n"
				+ "		INNER JOIN t_department ON t_major.did = t_department.did WHERE 1=1";
		// 可变字符串类
		StringBuilder strBuilder = new StringBuilder(sql);
		if (date != null) {
			strBuilder.append(" AND course_date = '" + date + "'");
		}
		if (dName != null) {
			strBuilder.append(" AND dname = '" + dName + "'");
		}
		if (major != null) {
			strBuilder.append(" AND major_name = '" + major + "'");
		}
		if (grade != null) {
			strBuilder.append(" AND grade = '" + grade + "'");
		}
		sql = strBuilder.toString();
		System.out.println(sql);

		try {
			stmt = connection.createStatement();
			rSet = stmt.executeQuery(sql);
			// 把rSet结果集合中数据集合到openclassest中
			while (rSet.next()) {
				OpenClass openclass = new OpenClass();
				openclass.setCid(rSet.getInt("cid"));
				openclass.setCredit(rSet.getInt("credit"));
				openclass.setNum(rSet.getInt("num"));
				openclass.setDate(rSet.getString("course_date"));
				openclass.setSpecial(rSet.getString("special"));
				openclass.setSpeaker(rSet.getString("speaker"));
				openclass.setOrganizer(rSet.getString("organizer"));
				openclass.setSite(rSet.getString("site"));
				openclass.setGroupShow(rSet.getString("group_show"));
				openclass.setWay(rSet.getString("way"));
				openclass.setRemark(rSet.getString("remark"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DbUtil.closeResource(connection, null, rSet);
		}
		return openclasses;
	}

}
